from datetime import datetime

import pandas as pd
import os
from django.contrib import auth
from django.contrib.auth.decorators import login_required
from django.db.models import Count, Sum
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render, redirect
from django.utils.safestring import mark_safe

from web.models import UserInfo, Order, Customer, Salesman
# 后面如果对接大数据HDFS就用这个
# from web.utils.getJsonList import get_json_list, merge_json


def load(request):
    """数据录入"""
    # 清空旧数据表
    Order.objects.all().delete()
    Customer.objects.all().delete()
    Salesman.objects.all().delete()
    # DataFrame 二维数组
    order_df = pd.read_excel(f"{os.getcwd()}/web/static/data/商城详细销售数据.xls", sheet_name="订单")
    for i, row in order_df.iterrows():
        # 先录入客户数据
        row_dict = row.to_dict()
        print(row_dict)
        try:
            Customer.objects.get(cid=row_dict["客户 ID"])
        except Customer.DoesNotExist:
            Customer.objects.create(cid=row_dict["客户 ID"], name=row_dict["客户名称"], category=row_dict["细分"], )
        Order.objects.create(
            id=row_dict["行 ID"], oid=row_dict["订单 ID"], created=row_dict["订单日期"], mail=row_dict["发货日期"],
            mail_type=row_dict["邮寄方式"], customer=Customer.objects.get(cid=row_dict["客户 ID"]),
            city=row_dict["城市"], province=row_dict["省/自治区"], nation=row_dict["国家"], area=row_dict["地区"],
            pid=row_dict["产品 ID"], category=row_dict["类别"], subcategory=row_dict["子类别"], name=row_dict["产品名称"],
            revenue=row_dict["销售额"], quantity=row_dict["数量"], discount=row_dict["折扣"], profit=row_dict["利润"],
        )
    #
    salesman_df = pd.read_excel(f"{os.getcwd()}/web/static/data/商城详细销售数据.xls", sheet_name="销售人员")
    for i, row in salesman_df.iterrows():
        row_dict = row.to_dict()
        print(row_dict)
        Salesman.objects.create(area=row_dict["地区"], manager=row_dict["地区经理"])
    return redirect('/')


@login_required
def get_sale_info(request):
    """配合前端Ajax 获取某省份的销售信息"""
    province = request.POST.get("province")
    info = []
    for dic in Order.objects.filter(province=province).values("city").annotate(sum=Sum("profit")):
        info.append({"city": dic["city"], "profit": dic["sum"]})
    return JsonResponse({
        "result": info,
    })


@login_required
def index(request):
    """销售数据总览"""
    # 地区销售热力图地图 中国地图热力图可视化 + Ajax动态刷新右侧信息栏（视图函数get_sale_info）
    # 大数据
    # map_data = []
    # for dic in merge_json(get_json_list(f"{os.getcwd()}/web/static/output/indexData")):
    #     map_data.append({"name": dic["province"], "value": dic["sum(profit)"]})
    map_data = []
    for dic in Order.objects.values("province").annotate(sum=Sum("profit")):
        map_data.append({"name": dic["province"], "value": round(float(dic["sum"]), 3)})

    return render(request, "index.html", {
        # 成交单数总量
        "order_sum": Order.objects.values("oid").distinct().count(),
        # 合作客户数量
        "customer_sum": Order.objects.values("customer__cid").distinct().count(),
        # 在售商品种类
        "product_type_count": Order.objects.values("category").distinct().count(),
        # 总利润统计
        "profit_sum": sum([dic["profit"] for dic in Order.objects.values("profit")]),
        # 地图数据
        "map_data": mark_safe(map_data),
    })


@login_required
def search(request):
    """检索订单"""
    order = None
    order_id = ""
    products = []
    if request.method == "POST":
        order_id = request.POST.get("oid", "")
        if order_id:
            products = Order.objects.filter(oid=order_id)
            order = Order.objects.filter(oid=order_id).first()

    return render(request, "search.html", {
        "order": order,
        "oid": order_id,
        "products": products,
    })


@login_required
def get_profit_info(request):
    """配合前端Ajax 获取某省份内利润信息"""
    province = request.POST.get("province")
    info = []
    for dic in Order.objects.filter(province=province).values("city").annotate(sum=Sum("profit")).order_by("-sum"):
        info.append({"city": dic["city"], "profit": dic["sum"]})
    return JsonResponse({
        # 只取前15数据，不够15个的就全显示
        "result": info[:15] if len(info) > 15 else info,
    })


@login_required
def push_pie2_data(request):
    """配合前端Ajax 刷新右侧饼状图数据"""
    category = request.POST.get("category")
    area = request.POST.get("area")
    info = []
    for dic in Order.objects.filter(category=category, area=area).values("subcategory").annotate(count=Count("oid")):
        info.append({"name": dic["subcategory"], "value": dic["count"]})
    return JsonResponse({
        "result": info,
    })


@login_required
def predict(request):
    """预测"""
    # 获取前端输入
    the_created = request.GET.get("year", "") + "-" + request.GET.get("month", "")
    the_city = request.GET.get("city", "")
    the_province = request.GET.get("province", "")
    the_nation = request.GET.get("nation", "")
    the_area = request.GET.get("area", "")
    try:
        the_revenue = float(request.GET.get("revenue"))
    except Exception as e:
        print(e)
        the_revenue = 0.0
    try:
        the_quantity = int(request.GET.get("quantity"))
    except Exception as e:
        print(e)
        the_quantity = 0

    if the_created and the_city and the_province and the_nation and the_area and the_revenue and the_quantity:
        # 模型训练
        objects = Order.objects.values(
            "created", "city", "province", "nation", "area", "revenue", "quantity", "profit"
        )  # 获取所有模型对象并转化为字典
        for dic in objects:
            dic["created"] = datetime.strftime(dic["created"], "%Y-%m")
        data = pd.DataFrame(list(objects))  # 将字典列表转化为DataFrame

        # 预测
        # 我们怎么实现 线性回归 BayesianRidge
        # x=1 y=1
        # x=2 y=2
        # x=3 y=3

        # 数据的要求：
        # 1. 每个字段都是数字

        # 编码
        # Label Encoder: LabelEncoder主要被用来将分类或者顺序数据编码为一组以0开始的连续整数。标签编码不适合所有情况，只有当分类变量之间有明显的排序关系时，才应使用标签编码。
        # OrdinalEncoder和LabelEncoder在功能上类似，只是OrdinalEncoder允许对多列分类特征进行转换，而LabelEncoder只能对单列进行编码。如果你的数据集中有多列需要转换为数值类型，OrdinalEncoder就非常有用了。
        from sklearn.preprocessing import LabelEncoder
        le = LabelEncoder()
        le.fit(list(data['created']) + [the_created])  # 将训练集 "created" 列和新输入的 "the_created" 一起做 fit
        data['created'] = le.transform(data['created'])  # 对 "created" 列数据进行 transform
        # One-Hot Encoder: OneHotEncoder用于将具有n_possible_values种可能值的列转换为n_possible_values个二异性特征。这通常用于无序离散特征。
        # 对于OneHotEncoder，你需要将输出转换为一个数组，因为它返回一个稀疏矩阵。你的数据需要被转换为一个2D矩阵，这将对你的数据进行封装，让它成为一个二维的numpy数组。
        from sklearn.preprocessing import OneHotEncoder
        encoder = OneHotEncoder()
        # 设置需要编码的列
        cols_to_encode = ['city', 'province', 'nation', 'area']
        # 对选定的列进行OneHot编码，并生成新的DataFrame
        encoded_cols = encoder.fit_transform(data[cols_to_encode])
        encoded_df = pd.DataFrame(encoded_cols.toarray(), columns=encoder.get_feature_names_out(cols_to_encode))
        for col in data.columns:
            if col not in cols_to_encode:
                encoded_df[col] = data[col]

        from sklearn import preprocessing
        import numpy as np
        # 获取编码后的列名，并加上其他列名
        encoded_feature_names = encoder.get_feature_names_out(cols_to_encode)
        other_cols = ['created', 'revenue', 'quantity']
        all_features = list(encoded_feature_names) + other_cols
        # sklearn库中提供的标准化方法：(num-avg)/标准差
        x = preprocessing.scale(np.array(encoded_df[all_features]))
        y = np.array(encoded_df[['profit']])

        from sklearn.linear_model import BayesianRidge
        # 线性回归 —— 贝叶斯岭回归算法
        lr = BayesianRidge()
        # 拟合
        lr.fit(x, y)

        created_encoded = le.transform([the_created])
        df_info = pd.DataFrame({
            'created': [created_encoded], 'city': [the_city], 'province': [the_province], 'nation': [the_nation],
            'area': [the_area], 'revenue': [the_revenue], 'quantity': [the_quantity],
        })
        encoded_cols_info = encoder.transform(df_info[cols_to_encode])
        encoded_df_info = pd.DataFrame(encoded_cols_info.toarray(), columns=encoder.get_feature_names_out(cols_to_encode))
        for col in df_info.columns:
            if col not in cols_to_encode:
                encoded_df_info[col] = df_info[col]
        np_info = np.array(encoded_df_info)
        predict_price = lr.predict(np_info.reshape(1, -1))
    else:
        predict_price = None

    return render(request, "predict.html", {
        "year": the_created.split("-")[0],
        "month": the_created.split("-")[1],
        "revenue": the_revenue,
        "quantity": the_quantity,
        "created": the_created,
        "city": the_city,
        "province": the_province,
        "nation": the_nation,
        "area": the_area,
        "predict_price": round(float(predict_price), 3) if predict_price else 0,
    })


@login_required
def chart(request):
    """区域经理业绩"""

    def get_salesman_by_area(area):
        return Salesman.objects.get(area=area)

    the_area = request.GET.get("area", Order.objects.all().first().area)
    salesman_obj = get_salesman_by_area(the_area)

    area_options_html = ""
    for dic in Order.objects.values("area").distinct():
        if the_area == dic['area']:
            area_options_html += f"<option selected>{dic['area']}</option>"
        else:
            area_options_html += f"<option>{dic['area']}</option>"

    # 不同省份利润组成 柱状图
    bar1_x = []
    bar1_y_1 = []
    bar1_y_2 = []
    for dic in Order.objects.filter(area=the_area).values("province").annotate(
            sum1=Sum("profit"), sum2=Sum("revenue")):
        bar1_x.append(dic["province"])
        bar1_y_1.append(round(float(dic["sum1"]), 3))
        bar1_y_2.append(round(float(dic["sum2"]), 3))
    # 不同省份利润组成 柱状图 大数据实现
    # bar1_x = []
    # bar1_y_1 = []
    # bar1_y_2 = []
    # for dic in merge_json(get_json_list(f"{os.getcwd()}/web/static/output/chartBar")):
    #     if dic["area"] == the_area:
    #         bar1_x.append(dic["province"])
    #         bar1_y_1.append(dic["sum(profit)"])
    #         bar1_y_2.append(dic["sum(revenue)"])
    # 最大最小值单独设置一个样式
    max_value = max(bar1_y_1)
    min_value = min(bar1_y_1)
    bar1_y_1[bar1_y_1.index(min_value)] = {
        "value": min_value,
        "itemStyle": {
            "color": '#a90000'  # #edafda
        }
    }
    bar1_y_1[bar1_y_1.index(max_value)] = {
        "value": max_value,
        "itemStyle": {
            "color": '#a90000'  # #edafda
        }
    }

    # 商品分类构成 饼状图 大数据
    # pie1_data = [
    #     {"name": dic["category"], "value": dic["count"]}
    #     for dic in merge_json(get_json_list(f"{os.getcwd()}/web/static/output/chartPie")) if dic["area"] == the_area
    # ]
    # 商品分类构成 饼状图
    pie1_data = [
        {"name": dic["category"], "value": dic["count"]}
        for dic in Order.objects.filter(area=the_area).values("category").annotate(count=Count("oid"))
    ]

    # 地区历史销售记录 折线图 大数据
    # date_group = {}
    # for dic in merge_json(get_json_list(f"{os.getcwd()}/web/static/output/chartLine")):
    #     if dic["area"] == the_area:
    #         if dic["created_month"] not in date_group:
    #             date_group[dic["created_month"]] = {dic["category"]: dic["revenue_sum"]}
    #         else:
    #             date_group[dic["created_month"]][dic["category"]] = dic["revenue_sum"]
    # 地区历史销售记录 折线图
    date_group = {}
    for dic in Order.objects.filter(
            area=the_area).values("category", "created").annotate(sum=Sum("revenue")).order_by("created"):
        month = datetime.strftime(dic["created"], "%Y-%m")
        if month not in date_group:
            date_group[month] = {dic["category"]: dic["sum"]}
        else:
            if dic["category"] not in date_group[month]:
                date_group[month][dic["category"]] = dic["sum"]
            else:
                date_group[month][dic["category"]] += dic["sum"]
    line_x, line_y_1, line_y_2, line_y_3 = [], [], [], []
    for month, dic in sorted(date_group.items(), key=lambda x: x[0]):
        line_x.append(month)
        line_y_1.append(round(float(dic.get("办公用品")), 3) if dic.get("办公用品") else 0)
        line_y_2.append(round(float(dic.get("技术")), 3) if dic.get("技术") else 0)
        line_y_3.append(round(float(dic.get("家具")), 3) if dic.get("家具") else 0)
    return render(request, "chart.html", {
        # 当前选择的地区信息
        "the_area": the_area,
        # 负责该区域的区域经理
        "salesman_obj": salesman_obj,
        # 下拉框html代码
        "area_options_html": mark_safe(area_options_html),
        # 该区域经理负责的 省份数量
        "province_count": Order.objects.filter(area=the_area).values("province").distinct().count(),
        # 该区域经理负责的 城市数量
        "city_count": Order.objects.filter(area=the_area).values("city").distinct().count(),
        # 该区域经理负责的 总销售额
        "revenue_sum": sum([dic["revenue"] for dic in Order.objects.filter(area=the_area).values("revenue")]),
        # 该区域经理负责的 总创收
        "profit_sum": sum([dic["profit"] for dic in Order.objects.filter(area=the_area).values("profit")]),
        # 不同省份利润组成 bar
        "bar1_x": mark_safe(bar1_x),
        "bar1_y_1": mark_safe(bar1_y_1),
        "bar1_y_2": mark_safe(bar1_y_2),
        # 商品分类构成 pie
        "pie1_data": mark_safe(pie1_data),
        # 地区历史销售记录
        "line_x": mark_safe(line_x),
        "line_y_1": mark_safe(line_y_1),
        "line_y_2": mark_safe(line_y_2),
        "line_y_3": mark_safe(line_y_3),
    })


def get_valid_code_img(request):
    """获取验证码"""
    from web.utils import get_valid
    data = get_valid.get_validCode_img(request)
    return HttpResponse(data)


def login(request):
    """基于ajax实现登录功能"""
    if request.method == "POST":
        response = {
            "user": None,
            "msg": None,
        }
        user = request.POST.get("user")
        pwd = request.POST.get("pwd")
        valid_code = request.POST.get("valid_code")
        if valid_code.upper() == request.session.get("valid_code_str").upper():
            # Django Auth组件  123 --MD5-- 密文
            user = auth.authenticate(username=user, password=pwd)
            if user:
                # request.user == 当前用户
                auth.login(request, user)
                response["user"] = user.username
            else:
                response["msg"] = "用户名或密码错误！"
        else:
            response["msg"] = "验证码错误！"
        return JsonResponse(response)
    return render(request, 'login.html')


def register(request):
    """基于django的Form组件和ajax实现注册界面"""
    from web.utils.my_forms import UserForm
    if request.is_ajax():
        form = UserForm(request.POST)
        response = {"user": None, "msg": None}
        if form.is_valid():
            response["user"] = form.cleaned_data.get("user")
            # 生成一条用户记录
            user = form.cleaned_data.get("user")
            pwd = form.cleaned_data.get("pwd")
            email = form.cleaned_data.get("email")
            avatar_obj = request.FILES.get("avatar")
            extra = {}
            if avatar_obj:
                # 上传头像就用用户传的头像
                # 用户不上传图片就默认用default.png
                extra["avatar"] = avatar_obj
            UserInfo.objects.create_user(username=user, password=pwd, email=email, **extra)
        else:
            print(form.cleaned_data)
            print(form.errors)
            response["msg"] = form.errors
        return JsonResponse(response)

    form = UserForm()
    return render(request, "register.html", {"form": form})


def logout(request):
    """注销"""
    auth.logout(request)
    return redirect("/login/")
